package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.DBUtill;
import org.csu.mypetstore.persistence.ProductDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProductDAOImpl implements ProductDAO {
    public static final String getProduct = "SELECT PRODUCTID, NAME, DESCN as description, CATEGORY as categoryId FROM PRODUCT WHERE PRODUCTID = ?";
    public static final String getProductList = "SELECT PRODUCTID, NAME, DESCN as description, CATEGORY as categoryId FROM PRODUCT WHERE CATEGORY = ?";
    public static final String searchProductListString = "select PRODUCTID, NAME, DESCN as description, CATEGORY as categoryId from PRODUCT WHERE lower (name) like ?";
    @Override
    public List<Product> getProductListByCategory(String categoryId) {
        List<Product> productList = new ArrayList<Product>();
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(getProductList);
            pStatement.setString(1,categoryId);
            ResultSet resultSet = pStatement.executeQuery();
            while (resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                productList.add(product);
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return productList;
    }

    @Override
    public Product getProduct(String productId) {
        Product product = null;
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(getProduct);
            pStatement.setString(1,productId);
            ResultSet resultSet = pStatement.executeQuery();
            if (resultSet.next()){
                product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }

        return product;
    }

    @Override
    public List<Product> searchProductList(String keywords) {
        List<Product> productList = new ArrayList<Product>();
        try{
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(searchProductListString);
            pStatement.setString(1,keywords);
            ResultSet resultSet = pStatement.executeQuery();
            while (resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                productList.add(product);
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return productList;
    }


    @Override
    public List<Product> getSearchInfo(String data) {
        List<Product> productList = new ArrayList<Product>();
        try {
            Connection connection = DBUtill.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(searchProductListString);
            pStatement.setString(1,data);
            ResultSet resultSet = pStatement.executeQuery();
            while(resultSet.next()){
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                productList.add(product);
            }
            DBUtill.closeResultSet(resultSet);
            DBUtill.closePreparedStatement(pStatement);
            DBUtill.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return productList;

    }
    }
